﻿using System;
using System.Data.SqlServerCe;
using System.IO;

namespace PWDepositary
{
    class Database
    {
        public readonly static string DatabaseName = "wall.sdf";
        public static readonly string ConnectString = "Data Source = " +
                                                      Path.GetDirectoryName(
                                                          System.Reflection.Assembly.GetExecutingAssembly().GetName().
                                                              CodeBase)
                                                      + "\\" + DatabaseName + "; Password = ";
        public static string ConnectStringFull;
        private const string DataTableName = "PWTABLE";
        private const string IDColumn = "ID";
        private const string NameColumn = "NAME";
        private const string TagColumn = "TAG";
        private const string DetailsColumn = "DETAILS";
        private const string PassColumn = "PASSWORD";

        public static void CreateDatabase(string pass)
        {
            ConnectStringFull = ConnectString + pass;
            // Создаем базу
            SqlCeEngine sqlCeEngine = null;
            SqlCeConnection connection = null;
            try
            {
                sqlCeEngine = new SqlCeEngine(ConnectStringFull + "; encrypt database=true");
                sqlCeEngine.CreateDatabase();
                connection = new SqlCeConnection(ConnectStringFull);
                connection.Open();
                SqlCeCommand cmd = connection.CreateCommand();
                cmd.CommandText = "CREATE TABLE " + DataTableName + " (" +
                                  IDColumn + " INT IDENTITY NOT NULL PRIMARY KEY" + ", " +
                                  NameColumn + " nvarchar(30)" + ", " +
                                  TagColumn + " nvarchar(20)" + ", " +
                                  DetailsColumn + " nvarchar(300)" + ", " +
                                  PassColumn + " nvarchar(30)"
                                  + ")";
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.ToString());
            }
            finally
            {
                if (sqlCeEngine != null) sqlCeEngine.Dispose();
                if (connection != null) connection.Close();
            }
        }
        public static int SaveToDatabase(string name, string tag, string details, string pass)
        {
            SqlCeConnection connection = null;
            SqlCeCommand cmd = null;
            int i = -1;
            try
            {
                connection = new SqlCeConnection(ConnectStringFull);
                connection.Open();
                cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO " + DataTableName + " (" 
                                  +NameColumn + ", " 
                                  +TagColumn + ", " 
                                  +DetailsColumn + ", " 
                                  +PassColumn
                                  + " ) VALUES ('"
                                  + name + "', '"
                                  + tag + "', '"
                                  + details + "', '"
                                  + pass
                                  + "')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "select max("
                  + IDColumn + ")"
                  + " from " + DataTableName;
                i = (int)cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("Ошибка сохранения данных:" + e);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (connection != null) connection.Close();
            }
            return i;
        }

        public static void DeleteFromDatabase(int id)
        {
            SqlCeConnection connection = null;
            SqlCeCommand cmd = null;
            try
            {
                connection = new SqlCeConnection(ConnectStringFull);
                connection.Open();
                cmd = connection.CreateCommand();
                cmd.CommandText = "DELETE FROM " + DataTableName
                                  + " WHERE " + IDColumn + "=" + id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("Ошибка удаления данных:" + e);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (connection != null) connection.Close();
            }
        }

        public static void UpdateDatabase(int id, string name, string tag, string details, string pass)
        {
            SqlCeConnection connection = null;
            SqlCeCommand cmd = null;
            try
            {
                connection = new SqlCeConnection(ConnectStringFull);
                connection.Open();
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE " + DataTableName
                                  + " SET "
                                  + NameColumn + " = '" + name + "', "
                                  + TagColumn + " = '" + tag + "', "
                                  + DetailsColumn + " = '" + details + "', "
                                  + PassColumn + " = '" + pass + "'"
                                  + " where " + IDColumn + "=" + id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("Ошибка изменения данных:" + e);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (connection != null) connection.Close();
            }
        }

        public static System.Data.IDataReader GetListPassword()
        {
            SqlCeDataReader myReader = null;
            try
            {

                var connection = new SqlCeConnection(ConnectStringFull);
                connection.Open();
                SqlCeCommand cmd = connection.CreateCommand();
                cmd.CommandText = "select "
                                  + IDColumn + ", "
                                  + NameColumn + ", "
                                  + TagColumn + ", "
                                  + DetailsColumn + ", "
                                  + PassColumn
                                  + " from " + DataTableName;

                myReader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
            }
            catch
            {
            }
            return myReader;
        }
        public static void ChangePassBd(string newpass)
        {
            SqlCeEngine sqlCeEngine = null;
            try
            {
                sqlCeEngine = new SqlCeEngine(@ConnectStringFull);
                sqlCeEngine.Compact(@"Data Source=newdb.sdf; Password = 123456");
            }
            catch (SqlCeException e)
            {
                System.Windows.Forms.MessageBox.Show(e.ToString());
            }
            finally
            {
                if (sqlCeEngine != null) sqlCeEngine.Dispose();
            }

        }
    }
}
